﻿/****************************************************************************
Copyright (c) 2014-2015 凌惊雪 微信:Lingjingxue 邮箱:lingjingxue@sina.com QQ:271487457

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in
all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
THE SOFTWARE.
****************************************************************************/

using MySql.Data.MySqlClient;
using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using UtilPublic.ServerLoger;

namespace UtilPublic.Mysql
{
    /// <summary>
    /// 数据库Mysql处理
    /// </summary>
    public static class DbMysqlStatic
    {
        /// <summary>
        /// 数据库连接
        /// </summary>
        private static MySqlConnection DataBaseUser = null;

        /// <summary>
        /// 数据库指令
        /// </summary>
        private static MySqlCommand DataBaseCmd;

        /// <summary>
        /// 连接状态
        /// </summary>
        private static int DataBaseState = 0;

        /// <summary>
        /// 连接到数据库
        /// </summary>
        /// <param name="connectionstring"></param>
        public static void DbMysqlConnect(string connectionstring)
        {
            try
            {
                DataBaseUser = new MySqlConnection(connectionstring);
                DataBaseCmd = DataBaseUser.CreateCommand();
                DataBaseCmd.CommandType = CommandType.Text;

                DataBaseCmd.Connection.Open();
                DataBaseState = 1;
            }
            catch
            {
                DataBaseState = 0;
            }
        }
        /// <summary>
        /// 判断连接
        /// </summary>
        /// <returns></returns>
        public static bool IsConnect()
        {
            if (DataBaseState == 1)
            {
                return DataBaseUser.Ping();
            }
            return false;
        }

        /// <summary>
        /// 执行数据库语句
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public static bool RunCommand(string cmd)
        {
            if (DataBaseState != 1)
            {
                return false;
            }
            try
            {
                DataBaseCmd.CommandText = cmd;
                DataBaseCmd.ExecuteNonQuery();
            }
            catch
            {
                return false;
            }
            return true;
        }

        /// <summary>
        /// 执行数据库查询
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public static MySqlDataReader RunReader(string cmd)
        {
            if (DataBaseState != 1)
            {
                return null;
            }
            DataBaseCmd.CommandText = cmd;
            return DataBaseCmd.ExecuteReader();
        }

        /// <summary>
        /// 执行数据库插入
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="datas">数据</param>
        /// <returns></returns>
        public static bool RunInsert(string tablename, Dictionary<string, object> datas)
        {
            if (datas.Count > 0)
            {
                var sbk = new StringBuilder();
                var sbv = new StringBuilder();
                int index = 0;
                foreach (var kvp in datas)
                {
                    index++;
                    sbk.Append(kvp.Key);
                    sbv.Append($"'{kvp.Value}'");
                    if (index != datas.Count)
                    {
                        sbk.Append(", ");
                        sbv.Append(", ");
                    }
                }
                string cmd = $"insert into {tablename} ({sbk}) values ({sbv})";
                return RunCommand(cmd);
            }
            return true;
        }

        /// <summary>
        /// 执行数据库更新
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="datas">数据</param>
        /// <param name="dataswhere">条件</param>
        /// <returns></returns>
        public static bool RunUpdate(string tablename, Dictionary<string, object> datas, Dictionary<string, object> dataswhere)
        {
            if (datas.Count > 0)
            {
                var sbu = new StringBuilder();
                int index = 0;
                foreach (var kvp in datas)
                {
                    index++;
                    sbu.Append($"{kvp.Key}='{kvp.Value}'");
                    if (index != datas.Count)
                    {
                        sbu.Append(", ");
                    }
                }
                string cmd = $"update {tablename} set {sbu}";
                if (dataswhere.Count > 0)
                {
                    var sbw = new StringBuilder();
                    int indexw = 0;
                    foreach (var kvpw in dataswhere)
                    {
                        indexw++;
                        sbw.Append($"{kvpw.Key}='{kvpw.Value}'");
                        if (indexw != dataswhere.Count)
                        {
                            sbw.Append(" and ");
                        }
                    }
                    cmd += $" where ({sbw})";
                }
                return RunCommand(cmd);
            }
            return true;
        }

        /// <summary>
        /// 执行数据库查询存在
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="dataswhere">条件</param>
        /// <returns></returns>
        public static bool RunCheckExist(string tablename, Dictionary<string, object> dataswhere)
        {
            bool isexist = false;
            string cmd = $"select * from {tablename} where 1=1";
            if (dataswhere.Count > 0)
            {
                var sbw = new StringBuilder();
                int indexw = 0;
                foreach (var kvpw in dataswhere)
                {
                    indexw++;
                    sbw.Append($"{kvpw.Key}='{kvpw.Value}'");
                    if (indexw != dataswhere.Count)
                    {
                        sbw.Append(" and ");
                    }
                }
                cmd = $"select * from {tablename} where ({sbw})";
            }
            MySqlDataReader reader = RunReader(cmd);
            try
            {
                while (reader.Read())
                {
                    if (reader.HasRows)
                    {
                        isexist = true;
                    }
                    else
                    {
                        isexist = false;
                    }
                }
            }
            catch
            {
            }
            finally
            {
                reader.Close();
            }
            return isexist;
        }

        /// <summary>
        /// 执行数据库读取
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="dataswhere">执行条件</param>
        /// <param name="datasread">返回字典</param>
        /// <returns></returns>
        public static bool RunReader(string tablename, Dictionary<string, object> dataswhere, ref Dictionary<string, object> datasread)
        {
            bool isexist = false;
            string cmd = $"select * from {tablename} where 1=1";
            if (dataswhere.Count > 0)
            {
                var sbw = new StringBuilder();
                int indexw = 0;
                foreach (var kvpw in dataswhere)
                {
                    indexw++;
                    sbw.Append($"{kvpw.Key}='{kvpw.Value}'");
                    if (indexw != dataswhere.Count)
                    {
                        sbw.Append(" and ");
                    }
                }
                cmd = $"select * from {tablename} where ({sbw})";
            }
            MySqlDataReader reader = RunReader(cmd);
            try
            {
                while (reader.Read())
                {
                    if (reader.HasRows)
                    {
                        isexist = true;
                        var datasreadkeys = new List<string>(datasread.Keys);
                        foreach (var item in datasreadkeys)
                        {
                            datasread[item] = reader[item];
                        }
                    }
                    else
                    {
                        isexist = false;
                    }
                }
            }
            catch (Exception ex)
            {
                loger.Fatal($"执行数据库读取错误", ex);
            }
            finally
            {
                reader.Close();
            }
            return isexist;
        }

        /// <summary>
        /// 执行数据库排序读取
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="listorders">排序条件</param>
        /// <param name="limit">选择个数</param>
        /// <param name="datasreadkeys">读取的key</param>
        /// <param name="listdatasread">返回结果</param>
        /// <returns></returns>
        public static bool RunOrderReader(string tablename, Dictionary<string, string> listorders, int limit, List<string> datasreadkeys, ref List<Dictionary<string, object>> listdatasread)
        {
            bool isexist = false;
            string cmd = $"select * from {tablename} limit {limit}";
            if (listorders.Count > 0)
            {
                var sbw = new StringBuilder();
                int indexw = 0;
                foreach (var kvpw in listorders)
                {
                    indexw++;
                    sbw.Append($"{kvpw.Key} {kvpw.Value}");
                    if (indexw != listorders.Count)
                    {
                        sbw.Append(", ");
                    }
                }
                cmd = $"select * from {tablename} ORDER BY {sbw} limit {limit}";
            }
            MySqlDataReader reader = RunReader(cmd);
            try
            {
                while (reader.Read())
                {
                    if (reader.HasRows)
                    {
                        isexist = true;
                        var datasread = new Dictionary<string, object>();
                        foreach (var item in datasreadkeys)
                        {
                            datasread[item] = reader[item];
                        }
                        listdatasread.Add(datasread);
                    }
                    else
                    {
                        isexist = false;
                    }
                }
            }
            catch (Exception ex)
            {
                loger.Fatal($"执行数据库读取错误", ex);
            }
            finally
            {
                reader.Close();
            }
            return isexist;
        }
    }


}